package sqlexplorer.application;

import java.security.InvalidParameterException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Observable;

import sqlexplorer.application.exceptions.NotConnectedException;
import sqlexplorer.domain.resultset.QueryResultSet;

public class SqlController extends Observable{
	
	private static SqlController m_instance;
	
	private QueryResultSet m_result;
	
	private SqlController(){
		m_result=null;
		
		//test code
		String[] columnNames=new String[]{"col1","col2"};
		HashMap<String,List<String>> values=new HashMap<String,List<String>>();
		
		for(int i=0; i<columnNames.length; i++){
			values.put(columnNames[i],new ArrayList<String>());
		}
		
		values.get(columnNames[0]).add("value col1");
		values.get(columnNames[0]).add("value col1");
		values.get(columnNames[1]).add("value col2");
		values.get(columnNames[1]).add("value col2");
		
		m_result=new QueryResultSet(columnNames,values);
	}
	
	public static SqlController getInstance(){
		if(m_instance==null){
			m_instance=new SqlController();
		}
		
		return m_instance;
	}
	
	public void executeSql(String sql) throws InvalidParameterException,NotConnectedException,SQLException{
		if((sql!=null) && (sql.equals("")==false)){
			Connection connection=ConnectionsController.getInstance().getConnection();
			Statement statement=null;
			ResultSet resultSet=null;
			
			if(connection!=null){
				try{
					connection.setAutoCommit(true);
					statement=connection.createStatement();
					
					if(sql.toUpperCase().indexOf("SELECT")==0){
						//it is a select
						resultSet=statement.executeQuery(sql);
						
						//map the column names
						ResultSetMetaData rsMetaData=resultSet.getMetaData();
						String[] columnNames=new String[rsMetaData.getColumnCount()];
						
						for(int i=0; i<columnNames.length; i++){
							columnNames[i]=rsMetaData.getColumnLabel(i+1);
						}
						
						//map to own result set values
						HashMap<String,List<String>> values=new HashMap<String,List<String>>();
						
						for(int i=0; i<columnNames.length; i++){
							values.put(columnNames[i],new ArrayList<String>());
						}
						
						while(resultSet.next()){
							for(int i=0; i<columnNames.length; i++){
								values.get(columnNames[i]).add(resultSet.getString((i+1)));
							}
						}
						
						m_result=new QueryResultSet(columnNames,values);
						
						setChanged();
						notifyObservers("queryFinished");
					}else{
						statement.executeUpdate(sql);
						
						setChanged();
						notifyObservers("statementFinished");
					}
				}finally{
					try{
						if(resultSet!=null){
							resultSet.close();
						}
					}catch(Exception exc){}
					
					try{
						if(statement!=null){
							statement.close();
						}
					}catch(Exception exc){}
				}
			}else{
				throw new NotConnectedException();
			}
		}else{
			throw new InvalidParameterException();
		}
	}
	
	public QueryResultSet getQueryResultSet(){
		return m_result;
	}
}
